﻿using GKQC.Common;
using GKQC.DbHelper.Dao;
using GKQC.DbHelper.DbModels;
using GKQC.DbHelper.ErpDbModels;
using GKQC.DbHelper.ViewModels;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace GKQC.DbHelper.Services
{
    public class FqcService
    {
        AllDbContext dbContext;

        public FqcService(AllDbContext dbContext)
        {
            this.dbContext = dbContext;
        }

        public int GetSNo(string lotNo)
        {
            int sno = 0;
            if (string.IsNullOrWhiteSpace(lotNo)) return 1;

            string prefix = lotNo;
            string sql = "select max(SNo) from t_fqc_head where LotNo=@lotno";
            var dt = dbContext.QcDb.Ado.GetDataTable(sql, new SugarParameter[]{
              new SugarParameter("@lotno",lotNo)
            });

            if (dt != null && dt.Rows.Count > 0)
            {
                int.TryParse(dt.Rows[0][0].ToString(), out sno);
                if (sno > 0) sno++;
            }

            if (sno == 0) sno = 1;
            return sno;
        }

        public List<FqcDataModel> GetDatas(long headId)
        {
            return dbContext.QcDb.Queryable<FqcDataModel>()
                .Where(d => d.HeadId == headId)
                .ToList();
        }

        public List<FqcHeadModel> GetHeadsByLotNo(string lotNo)
        {
            var v = dbContext.QcDb.Queryable<FqcHeadModel>().Where(o => o.LotNo == lotNo);
            if (v != null)
                return v.ToList();
            else return new List<FqcHeadModel>();
        }

        /// <summary>
        /// 取最近的一条记录
        /// </summary>
        /// <param name="lotNo"></param>
        /// <returns></returns>
        public FqcHeadModel GetHeadByLotNo(string lotNo)
        {
            return dbContext.QcDb.Queryable<FqcHeadModel>().Where(o => o.LotNo == lotNo)
                .OrderBy(o=>o.CreateDate, OrderByType.Desc)
                .First();
        }

        /// <summary>
        /// 取最近的一条记录
        /// </summary>
        /// <param name="lotNo"></param>
        /// <returns></returns>
        public FqcHeadModel GetHeadByPn(string pn)
        {
            var v = dbContext.QcDb.Queryable<FqcHeadModel>().Where(o => o.Pn == pn)
                .OrderBy(o => o.CreateDate, OrderByType.Desc);
            if (v != null && v.Count() > 0)
                return v.First();
            else return null;
        }

        public FqcHeadModel GetHeadByLotNo(string lotNo,int sno)
        {
            var heads = dbContext.QcDb.Queryable<FqcHeadModel>()
                .Where(o => o.LotNo == lotNo)
                .Where(o=>o.SNo == sno);
            if (heads != null && heads.Count() > 0)
                return heads.First();
            else
                return null;
        }

        public FqcHeadModel GetHeadById(long id)
        {
            var heads = dbContext.QcDb.Queryable<FqcHeadModel>().Where(o => o.Id == id);
            if (heads != null && heads.Count() > 0)
                return heads.First();
            else
                return null;
        }

        public List<FqcHeadModel> GetHeads(int count)
        {
            return dbContext.QcDb.Queryable<FqcHeadModel>()
                .OrderBy(o => o.TestDate, OrderByType.Desc)
                .Take(count).ToList();
        }

        public int Save(FqcHeadModel head)
        {
            int i = 0;

            bool isNew = false;
            try
            {
                dbContext.QcDb.Ado.BeginTran();

                if (head.Id <= 0)
                {
                    if (!dbContext.QcDb.Insertable(head).ExecuteCommandIdentityIntoEntity())
                        throw new Exception("U0001: 新增保存出錯");

                    isNew = true;
                }

                List<FqcDataModel> datas = new List<FqcDataModel>();
                dbContext.QcDb.Deleteable<FqcDataModel>()
                    .Where(o => o.HeadId == head.Id)
                    .ExecuteCommand();

                if (head.ElectricViewModels != null && head.ElectricViewModels.Count > 0)
                {
                    datas.AddRange(ViewModelTrans<FqcDataModel>.ElectricViewToData(head.Id, head.SNo, head.ElectricViewModels));
                }
                if (head.SizeViewModels != null && head.SizeViewModels.Count > 0)
                {
                    datas.AddRange(ViewModelTrans<FqcDataModel>.SizeViewToData(head.Id, head.SNo, head.SizeViewModels));
                }

                var overLengths = datas.Where(d => d.Value > 9999999999);
                if (overLengths != null && overLengths.Count() > 0)
                    overLengths.ToList().ForEach(o => o.Value = 9999999999);
                var overLengths2 = datas.Where(d => d.Value < -9999999999);
                if (overLengths2 != null && overLengths2.Count() > 0)
                    overLengths2.ToList().ForEach(o => o.Value = -9999999999);

                if (datas.Count > 0)
                    dbContext.QcDb.Insertable(datas)
                    .ExecuteCommand();
                if (!isNew)
                    i = dbContext.QcDb.Updateable(head)
                        .ExecuteCommand();
                else
                    i = 1;

                dbContext.QcDb.Ado.CommitTran();
                head.Datas.Clear();
                head.Datas.AddRange(datas);
            }
            catch (Exception ex)
            {
                dbContext.QcDb.Ado.RollbackTran();

                Logger.LogError(ex);
                if (ex.Message.Contains("idx_t_fqc_head_lotno"))
                {
                    head.SNo += 1;
                    try
                    {
                        i = Save(head);
                    }
                    catch(Exception ex2)
                    {
                        throw ex2;
                    }
                }
                else throw ex;
            }

            return i;
        }

        /// <summary>
        /// 複製，不包含檢測數據
        /// </summary>
        /// <param name="FqcHeadModel"></param>
        /// <returns></returns>
        public FqcHeadModel Clone(FqcHeadModel FqcHeadModel)
        {
            FqcHeadModel newModel;
            if (FqcHeadModel == null)
                newModel = new FqcHeadModel();
            else newModel = ClassClone<FqcHeadModel, FqcHeadModel>.Clone(FqcHeadModel);
            newModel.Id = 0;
            newModel.ModiDate = null;
            newModel.ModiUser = string.Empty;

            return newModel;
        }

        /// <summary>
        /// 審核
        /// </summary>
        /// <param name="ortList"></param>
        /// <returns></returns>
        public int Approve(List<FqcHeadModel> ortList)
        {
            int i = 0;
            i = dbContext.QcDb.Updateable(ortList)
                .UpdateColumns(it => new { it.ApproveUser, it.ApproveDate })
                .ExecuteCommand();
            return i;
        }


        /// <summary>
        /// 反審核
        /// </summary>
        /// <param name="headList"></param>
        /// <returns></returns>
        public int UnApprove(List<FqcHeadModel> headList)
        {
            int i = 0;
            if (headList != null && headList.Count > 0)
            {
                i = dbContext.QcDb.Updateable(headList)
                    .UpdateColumns(it => new { it.ApproveUser, it.ApproveDate})
                    .ExecuteCommand();
            }
            return i;
        }

        /// <summary>
        /// 刪除
        /// </summary>
        /// <param name="heads"></param>
        /// <returns></returns>
        public int Delete(List<FqcHeadModel> heads)
        {
            int i = 0;
            try
            {
                dbContext.QcDb.Ado.BeginTran();
                dbContext.QcDb.Deleteable<FqcDataModel>()
                    .In(it => it.HeadId, heads.Select(o => o.Id).ToArray())
                    .ExecuteCommand();
                i = dbContext.QcDb.Deleteable(heads)
                    .ExecuteCommand();
                dbContext.QcDb.Ado.CommitTran();
            }
            catch(Exception ex)
            {
                dbContext.QcDb.Ado.RollbackTran();
                Logger.LogError(ex);
                throw ex;
            }
            return i;
        }

        public List<FqcHeadModel> Query(FqcFilter filter)
        {
            if (filter == null) return GetHeads(100);

            return dbContext.QcDb.Queryable<FqcHeadModel>()
                .WhereIF(!string.IsNullOrWhiteSpace(filter.SourceNo) && filter.SourceNo.Length ==14
                    , t => t.SourceNo.StartsWith(filter.SourceNo))
                .WhereIF(!string.IsNullOrWhiteSpace(filter.SourceNo) && filter.SourceNo.Length > 14
                    , t => t.SourceNo == filter.SourceNo)
                .WhereIF(!string.IsNullOrWhiteSpace(filter.LotNo), t => t.LotNo == filter.LotNo)
                .WhereIF(!string.IsNullOrWhiteSpace(filter.Customer), t => t.Customer == filter.Customer)
                .WhereIF(!string.IsNullOrWhiteSpace(filter.Pn), t => t.Pn == filter.Pn)
                .WhereIF(!string.IsNullOrWhiteSpace(filter.Series), t => t.Series == filter.Series)
                .WhereIF(!string.IsNullOrWhiteSpace(filter.Spec), t => t.Spec == filter.Spec)
                .WhereIF(!string.IsNullOrWhiteSpace(filter.Size), t => t.Size == filter.Size)
                .WhereIF(filter.IsTestDate, t => t.TestDate >= filter.StartTestDate.Date)
                .WhereIF(filter.IsTestDate, t => t.TestDate < filter.EndTestDate.AddDays(1).Date)
                .WhereIF(filter.ReportId > 0, t => t.ReportId == filter.ReportId)
                .WhereIF(filter.IsApprove && !filter.IsUnApprove, t => !SqlFunc.IsNullOrEmpty(t.ApproveUser))
                .WhereIF(!filter.IsApprove && filter.IsUnApprove, t => SqlFunc.IsNullOrEmpty(t.ApproveUser))
                .WhereIF(filter.IsModiDate, t => t.ModiDate >= filter.StartModiDate.Date)
                .WhereIF(filter.IsModiDate, t => t.ModiDate < filter.EndModiDate.AddDays(1).Date)
                .WhereIF(!string.IsNullOrWhiteSpace(filter.CreateUser), t => t.CreateUser == filter.CreateUser)
                .OrderBy(t => t.TestDate, OrderByType.Desc)
                .Take(filter.RowCount).ToList();

        }

        public FqcHeadModel QueryBySourceNo(string sourceNo)
        {
            return dbContext.QcDb.Queryable<FqcHeadModel>()
                .Where(t=>t.SourceNo == sourceNo)
                .OrderBy(t => t.TestDate, OrderByType.Desc)
                .Take(1).ToList().FirstOrDefault();
        }

        /// <summary>
        /// 通过工单号查最近的检测记录
        /// </summary>
        /// <param name="sourceNo"></param>
        /// <returns></returns>
        public FqcHeadModel QueryBySfb01(string sfb01)
        {
            return dbContext.QcDb.Queryable<FqcHeadModel>()
                .Where(t => t.SourceNo.StartsWith(sfb01))
                .OrderBy(t => t.TestDate, OrderByType.Desc)
                .Take(1).ToList().FirstOrDefault();
        }

        /// <summary>
        /// 通过工单号查最近的检测记录
        /// </summary>
        /// <param name="sourceNo"></param>
        /// <returns></returns>
        public List<FqcHeadModel> QuerysBySfb01(string sfb01)
        {
            return dbContext.QcDb.Queryable<FqcHeadModel>()
                .Where(t => t.SourceNo.StartsWith(sfb01))
                .OrderBy(t => t.TestDate, OrderByType.Desc)
                .Take(1).ToList();
        }

        public void GenStandard(FqcHeadModel head)
        {
            try
            {
                head.Standard.DfMax = head.DfMax;
                head.Standard.CapMax = head.CapMax;
                head.Standard.CapMin = head.CapMin;
                head.Standard.LcMax = head.LcMax;
                head.Standard.ZMax = head.ZMax;
                head.Standard.EsrMax = head.EsrMax;
                head.Standard.DMax = head.DMax;
                head.Standard.LMax = head.LMax;
                head.Standard.PMax = head.BMax; //束腰上限

                if (head.Standard.ZMax <= 0 && head.Standard.EsrMax > 0) 
                    head.Standard.ZMax = head.Standard.EsrMax;

                if (head.Standard.EsrMax <= 0 && head.Standard.ZMax > 0)
                    head.Standard.EsrMax = head.Standard.ZMax;


                Double D = ProductCode.GetD(head.Pn);
                if(D>0 && head.Standard.DMax<=0) head.Standard.DMax = ProductCode.GetD(head.Pn) + 0.5;
                double L = ProductCode.GetL(head.Pn);
                if (L> 0 && head.Standard.LMax <= 0) head.Standard.LMax = ProductCode.GetLMax(head.Pn);
            }
            catch (Exception ex)
            {
                Logger.LogError(ex);
                throw ex;
            }
        }

        public void UploadToErp(List<FqcHeadModel> approveList,out string msg)
        {
            msg = string.Empty;
            if (approveList == null || approveList.Count == 0) return;
            List<ItemCodeTran> trans = dbContext.QcDb.Queryable<ItemCodeTran>().ToList();
            if (!trans.Any())
            {
                msg = "未設置ERP檢測項目對照表t_sys_itemcode";
                return;
            }
             foreach (FqcHeadModel head in approveList)
            {
                if (head.Datas == null) continue;
                if (head.Datas.Count == 0)
                    head.Datas.AddRange(GetDatas(head.Id));
                List<tc_qdf_file> qdf = FqcDataToErpData(head, trans);
                if (qdf.Count == 0) continue;
                int i = ErpService.SaveData(dbContext,qdf);
                if (i > 0) msg = "上傳ERP成功";
            }
        }

        public void CancelUploadToErp(List<FqcHeadModel> approveList)
        {
            if (approveList == null || approveList.Count == 0) return;

            foreach (FqcHeadModel head in approveList)
            {
                ErpService.CancelFqcErpData(dbContext, head.SourceNo,head.SNo);
            }
        }

        /// <summary>
        /// FQC测试数据转换为ERP数据
        /// </summary>
        /// <param name="head"></param>
        /// <param name="ItemCodeTrans">检测项目代号对照表</param>
        /// <returns></returns>
        public static List<tc_qdf_file> FqcDataToErpData(FqcHeadModel head, List<ItemCodeTran> ItemCodeTrans)
        {
            List<tc_qdf_file> erpDatas = new List<tc_qdf_file>();
            if (string.IsNullOrWhiteSpace(head.SourceNo) || ItemCodeTrans == null || ItemCodeTrans.Count == 0) return erpDatas;

            if (head.Datas == null || head.Datas.Count == 0)
                return erpDatas;

            foreach (FqcDataModel fqc in head.Datas)
            {
                string erpItemCode = string.Empty;
                var tran = ItemCodeTrans.Where(t => t.ItemCode == fqc.ItemCode && t.ErpItemCode.StartsWith("F")).FirstOrDefault();
                if (tran == null) continue;
                tc_qdf_file qdf = new tc_qdf_file();
                qdf.tc_qdf01 = head.SourceNo;
                qdf.tc_qdf02 = tran.ErpItemCode;
                qdf.tc_qdf03 = fqc.Value;
                qdf.tc_qdf04 = head.SNo;
                qdf.tc_qdf05 = fqc.Value ==0 ? fqc.Valid: string.Empty;
                qdf.tc_qdfacti = "Y";
                erpDatas.Add(qdf);
            }

            return erpDatas;
        }
    }
}
